package model;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;

import org.apache.struts.upload.FormFile;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
import forms.ReviewerFormDelete;
import forms.ReviewerFormInsert;
//import forms.StudentFormUpdate;

public class ReviewerModel {
	
	private static String selectReviewerStr = "SELECT user_name FROM user_roles WHERE role = \'reviewer\' ORDER BY id";
	
	private static String selectAppReviewsStr = "SELECT s.first_name, s.middle_name, s.last_name FROM Users u, Students s, App_reviews app WHERE " + "" +
		"u.id=app.reviewer AND s.id=app.application AND app.finished=false AND u.user_name=?";

	private static String selectApplicationIdStr = "SELECT id FROM Students WHERE first_name = ? AND middle_name = ? AND last_name = ?";

	private static String selectOtherReviewsStr = "SELECT * FROM App_reviews WHERE finished=true AND application=?";
	
	private static String updateReviewStr = "UPDATE App_reviews SET finished=true, grade=?, comment=? " + 
		"WHERE application=? AND reviewer=?";
	
	private static String selectReviewerIdStr = "SELECT id FROM users WHERE user_name=?";
	
	private static String selectReviewCountStr = "SELECT COUNT(*) FROM App_reviews WHERE finished=true AND application=?";
	
	private static String selectMoreReviewersStr = "SELECT COUNT(*) FROM app_reviews WHERE reviewer = ? AND application = ?;";
	
	private static String insertNewReviewStr = "INSERT INTO App_reviews (application, reviewer, finished) VALUES (?, ?, false)";
	
	private static String updateAdmissionStatusStr = "UPDATE students SET admission_status = ? WHERE id = ?";
	
	public static CachedRowSet getAllReviewers() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectReviewerStr);

			// Use the created statement to SELECT
			// all the reviewers FROM the users table.
			ResultSet allReviewers = pStmt.executeQuery();

			CachedRowSet crsReviewers = new CachedRowSetImpl();
			crsReviewers.populate(allReviewers);

			allReviewers.close();
			pStmt.close();
			conn.close();

			return crsReviewers;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static CachedRowSet getAllAppReviews(String reviewer) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectAppReviewsStr);
			pStmt.setString(1, reviewer);
			// Use the created statement to SELECT
			// all application reviews for the reviewer given.
			ResultSet allAppReviews = pStmt.executeQuery();

			CachedRowSet crsAppReviews = new CachedRowSetImpl();
			crsAppReviews.populate(allAppReviews);

			allAppReviews.close();
			pStmt.close();
			conn.close();

			return crsAppReviews;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static int getApplicationId(String first, String middle, String last) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectApplicationIdStr);
			pStmt.setString(1, first);
			pStmt.setString(2, middle);
			pStmt.setString(3, last);
			
			// Use the created statement to SELECT
			// all applicant info.
			ResultSet allAppInfo = pStmt.executeQuery();

			CachedRowSet crsAppInfo = new CachedRowSetImpl();
			crsAppInfo.populate(allAppInfo);
			
			int applicationID = -1;
			while(crsAppInfo.next())
				applicationID = crsAppInfo.getInt("id");
				

			allAppInfo.close();
			pStmt.close();
			conn.close();

			return applicationID;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static CachedRowSet getOtherReviews(int student) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectOtherReviewsStr);
			pStmt.setInt(1, student);
			
			// Use the created statement to SELECT
			// all other reviews.
			ResultSet allOtherReviews = pStmt.executeQuery();

			CachedRowSet crsOtherReviews = new CachedRowSetImpl();
			crsOtherReviews.populate(allOtherReviews);
			
			allOtherReviews.close();
			pStmt.close();
			conn.close();

			return crsOtherReviews;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static void updateReview(String grade, String comment, 
			String first, String middle, String last, String user) throws DbException {
		try {
			Connection conn = DbConnectionPool.getConnection();
			
			// Get the student id to update with
			int student_id = getApplicationId(first, middle, last);
			
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectReviewerIdStr);
			pStmt.setString(1, user);
			

			ResultSet allReviewers = pStmt.executeQuery();
			
			int reviewer_id = -1;
			while(allReviewers.next())
				reviewer_id = allReviewers.getInt("id");
			
			pStmt.close();
			allReviewers.close();
			
			pStmt = conn.prepareStatement(updateReviewStr);
			pStmt.setInt(1, Integer.parseInt(grade));
			pStmt.setString(2, comment);
			pStmt.setInt(3, student_id);
			pStmt.setInt(4, reviewer_id);
			
			pStmt.executeUpdate();

			pStmt.close();
			conn.close();
			

			} catch (SQLException ex) {
				throw new DbException(ex);
			} catch (NamingException ex) {
				throw new DbException(ex);
			}
	}
	
	public static int getReviewCount(String first, String middle, String last) throws DbException {
		try {
			Connection conn = DbConnectionPool.getConnection();
			
			// Get the student id to update with
			int student_id = getApplicationId(first, middle, last);
			
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectReviewCountStr);
			pStmt.setInt(1, student_id);
			

			ResultSet reviewCount = pStmt.executeQuery();
			
			int num = -1;
			while(reviewCount.next())
				num = reviewCount.getInt("count");
			
			reviewCount.close();
			pStmt.close();
			conn.close();
			
			return num;

			} catch (SQLException ex) {
				throw new DbException(ex);
			} catch (NamingException ex) {
				throw new DbException(ex);
			}
	}
	
	public static Vector<String> getMoreReviewers(String first, String middle, String last) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// 1. Get the id that corresponds to the application of interest
			int student_id = getApplicationId(first, middle, last);

			// 2. Get the list of all reviewers
			PreparedStatement pStmt = conn.prepareStatement(selectReviewerStr);
			
			ResultSet allReviewers = pStmt.executeQuery();
			Vector<String> otherReviewersVector = new Vector<String>();
			
			while(allReviewers.next())
			{
				// 3. Get the id of the current reviewer in the loop
				pStmt = conn.prepareStatement(selectReviewerIdStr);
				pStmt.setString(1, allReviewers.getString("user_name"));
				ResultSet getIds = pStmt.executeQuery();
				int reviewer_id = -1;
				while(getIds.next())
					reviewer_id = getIds.getInt("id");
				
				// 4. Enter the reviewer and application info, query to see if review exists
				pStmt = conn.prepareStatement(selectMoreReviewersStr);
				pStmt.setInt(1, reviewer_id);
				pStmt.setInt(2, student_id);
				
				ResultSet count = pStmt.executeQuery();
				// 5. If the count > 0, then review already exists
				int exists = 0;
				while(count.next())
					exists = count.getInt("count");
				// 6. But if this review does not exist, enter into returned vector
				if(exists == 0)
					otherReviewersVector.add(allReviewers.getString("user_name"));
								
			}
			

			allReviewers.close();
			pStmt.close();
			conn.close();

			return otherReviewersVector;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	
	public static void insertReview(String first, String middle, String last, String user) throws DbException {
		try {
			Connection conn = DbConnectionPool.getConnection();
			
			// Get the student id to update with
			int student_id = getApplicationId(first, middle, last);
			
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectReviewerIdStr);
			pStmt.setString(1, user);
			

			ResultSet allReviewers = pStmt.executeQuery();
			
			int reviewer_id = -1;
			while(allReviewers.next())
				reviewer_id = allReviewers.getInt("id");
			
			pStmt.close();
			allReviewers.close();
			
			pStmt = conn.prepareStatement(insertNewReviewStr);
			pStmt.setInt(1, student_id);
			pStmt.setInt(2, reviewer_id);
			
			pStmt.executeUpdate();

			pStmt.close();
			conn.close();
			

			} catch (SQLException ex) {
				throw new DbException(ex);
			} catch (NamingException ex) {
				throw new DbException(ex);
			}
	}
	
}